# import os
# import mysql.connector
# from mysql.connector import Error
# from configparser import ConfigParser
# from typing import Dict, List, Tuple, Optional, Any


# def _resolve_config_path(config_path: str) -> str:
#     """
#     解析配置文件路径，支持相对路径和绝对路径
#     :param config_path: 输入的路径（如"../config/config.ini"或"/etc/config.ini"）
#     :return: 解析后的绝对路径
#     """
#     # 转换为绝对路径（处理相对路径）
#     absolute_path = os.path.abspath(config_path)
#     # 检查路径是否存在
#     if not os.path.exists(absolute_path):
#         raise FileNotFoundError(f"配置文件不存在：{absolute_path}")
#     return absolute_path


# class MySQLConfig:
#     """数据库配置类，支持加载多数据库配置并指定连接目标，支持指定任意路径的配置文件"""

#     def __init__(self, config_path: str = "config.ini", section: str = "mysql_dev"):
#         """
#         初始化配置
#         :param config_path: config.ini的绝对路径或相对路径（支持任意目录）
#         :param section: 要加载的数据库节点名称
#         """
#         self.config = ConfigParser()
#         self.section = section
#         # 处理配置文件路径（转换为绝对路径，便于调试）
#         self.config_path = _resolve_config_path(config_path)
#         self._load_config()

#     def _load_config(self) -> None:
#         """从指定路径的配置文件加载参数（指定编码格式）"""
#         # 关键修改：读取文件时指定encoding="utf-8"
#         if not self.config.read(self.config_path, encoding="utf-8"):
#             raise IOError(f"无法读取配置文件：{self.config_path}（请检查路径和编码）")

#         if not self.config.has_section(self.section):
#             raise ValueError(f"配置文件 {self.config_path} 中不存在节点 {self.section}")

#         # 读取配置参数（保持不变）
#         self.host = self.config.get(self.section, "host", fallback="localhost")
#         self.port = self.config.getint(self.section, "port", fallback=3306)
#         self.user = self.config.get(self.section, "user", fallback="root")
#         self.password = self.config.get(self.section, "password", fallback="")
#         self.database = self.config.get(self.section, "database", fallback="")

#     def get_all_sections(self) -> List[str]:
#         """获取所有数据库节点"""
#         return self.config.sections()

#     def switch_section(self, section: str) -> None:
#         """切换数据库节点"""
#         self.section = section
#         self._load_config()

#     def to_dict(self) -> Dict[str, Any]:
#         return {
#             "host": self.host,
#             "port": self.port,
#             "user": self.user,
#             "password": self.password,
#             "database": self.database
#         }


# class MySQLHandler:
#     """数据库操作类，支持多数据库配置切换"""

#     def __init__(self, config: MySQLConfig):
#         self.config = config
#         self.connection: Optional[mysql.connector.connection.MySQLConnection] = None
#         self.cursor: Optional[mysql.connector.cursor.MySQLCursor] = None
#         self._current_table: Optional[str] = None

#     def set_table(self, table_name: str) -> None:
#         self._current_table = table_name

#     def _check_table_set(self) -> bool:
#         if not self._current_table:
#             print("错误：请先调用set_table方法设置表名")
#             return False
#         return True

#     def connect(self) -> bool:
#         self.disconnect()
#         try:
#             self.connection = mysql.connector.connect(** self.config.to_dict())
#             if self.connection.is_connected():
#                 self.cursor = self.connection.cursor(dictionary=True)
#                 print(f"成功连接到数据库节点：{self.config.section}（配置文件：{self.config.config_path}）")
#                 return True
#         except Error as e:
#             print(f"数据库连接失败: {e}")
#         return False

#     def disconnect(self) -> None:
#         if self.cursor:
#             self.cursor.close()
#             self.cursor = None
#         if self.connection and self.connection.is_connected():
#             self.connection.close()
#             self.connection = None

#     def switch_database(self, section: str) -> bool:
#         try:
#             self.config.switch_section(section)
#             return self.connect()
#         except Exception as e:
#             print(f"切换数据库失败: {e}")
#             return False

#     # 以下为CRUD核心方法（与之前逻辑一致，依赖当前连接的数据库和表名）
#     def execute_query(self, query: str, params: Tuple[Any, ...] = ()) -> Optional[List[Dict[str, Any]]]:
#         if not self.connection or not self.connection.is_connected():
#             if not self.connect():
#                 return None

#         try:
#             self.cursor.execute(query, params)
#             return self.cursor.fetchall()
#         except Error as e:
#             print(f"查询执行失败: {e}")
#             return None

#     def execute_update(self, query: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
#         if not self.connection or not self.connection.is_connected():
#             if not self.connect():
#                 return None

#         try:
#             self.cursor.execute(query, params)
#             self.connection.commit()
#             return self.cursor.rowcount
#         except Error as e:
#             print(f"更新执行失败: {e}")
#             self.connection.rollback()
#             return None

#     def insert_one(self, data: Dict[str, Any]) -> Optional[int]:
#         if not self._check_table_set():
#             return None

#         columns = ', '.join(data.keys())
#         placeholders = ', '.join(['%s'] * len(data))
#         query = f"INSERT INTO {self._current_table} ({columns}) VALUES ({placeholders})"
#         return self.execute_update(query, tuple(data.values()))

#     def update(self, data: Dict[str, Any], condition: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
#         if not self._check_table_set():
#             return None

#         set_clause = ', '.join([f"{k} = %s" for k in data.keys()])
#         query = f"UPDATE {self._current_table} SET {set_clause} WHERE {condition}"
#         all_params = tuple(data.values()) + params
#         return self.execute_update(query, all_params)

#     def delete(self, condition: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
#         if not self._check_table_set():
#             return None

#         query = f"DELETE FROM {self._current_table} WHERE {condition}"
#         return self.execute_update(query, params)

#     def select(self,
#                columns: str = '*',
#                condition: str = '',
#                params: Tuple[Any, ...] = (),
#                order_by: str = '',
#                limit: int = 0) -> Optional[List[Dict[str, Any]]]:  # 新增limit参数
#         """查询数据（支持limit限制结果数量）"""
#         if not self._check_table_set():
#             return None

#         query = f"SELECT {columns} FROM {self._current_table}"
#         if condition:
#             query += f" WHERE {condition}"
#         if order_by:
#             query += f" ORDER BY {order_by}"
#         # 新增limit处理：如果传入limit>0，则添加LIMIT子句
#         if limit > 0:
#             query += f" LIMIT {limit}"

#         return self.execute_query(query, params)


# mysql_handler.py
import os
import mysql.connector
from mysql.connector import Error
from configparser import ConfigParser
from typing import Dict, List, Tuple, Optional, Any


class MySQLConfig:
    """数据库配置类，支持读取多环境配置和表名前缀"""
    
    def __init__(self, config_path: str = "config.ini", section: str = "mysql_dev"):
        """
        初始化配置
        :param config_path: 配置文件路径（绝对或相对路径）
        :param section: 数据库节点名称（如mysql_dev、mysql_prod）
        """
        self.config = ConfigParser()
        self.section = section
        self.config_path = self._resolve_config_path(config_path)
        self._load_config()

    def _resolve_config_path(self, config_path: str) -> str:
        """解析配置文件路径为绝对路径并检查存在性"""
        absolute_path = os.path.abspath(config_path)
        if not os.path.exists(absolute_path):
            raise FileNotFoundError(f"配置文件不存在：{absolute_path}")
        return absolute_path

    def _load_config(self) -> None:
        """从配置文件加载数据库参数（含表名前缀）"""
        if not self.config.read(self.config_path, encoding="utf-8"):
            raise IOError(f"无法读取配置文件：{self.config_path}")
        
        if not self.config.has_section(self.section):
            raise ValueError(f"配置文件中不存在节点 {self.section}")
        
        # 数据库连接参数
        self.host = self.config.get(self.section, "host", fallback="localhost")
        self.port = self.config.getint(self.section, "port", fallback=3306)
        self.user = self.config.get(self.section, "user", fallback="root")
        self.password = self.config.get(self.section, "password", fallback="")
        self.database = self.config.get(self.section, "database", fallback="")
        
        # 表名前缀配置（默认空字符串）
        self.table_prefix = self.config.get(self.section, "table_prefix", fallback="").strip()

    def get_all_sections(self) -> List[str]:
        """获取所有数据库节点"""
        return self.config.sections()

    def switch_section(self, section: str) -> None:
        """切换数据库节点并重新加载配置"""
        self.section = section
        self._load_config()

    def to_dict(self) -> Dict[str, Any]:
        """转换为数据库连接字典"""
        return {
            "host": self.host,
            "port": self.port,
            "user": self.user,
            "password": self.password,
            "database": self.database
        }


class MySQLHandler:
    """数据库操作类，支持表名前缀自动拼接和CRUD操作"""
    
    def __init__(self, config: MySQLConfig):
        self.config = config
        self.connection: Optional[mysql.connector.connection.MySQLConnection] = None
        self.cursor: Optional[mysql.connector.cursor.MySQLCursor] = None
        self._current_table: Optional[str] = None  # 原始表名（用户设置的表名）
        self._full_table: Optional[str] = None     # 带前缀的完整表名

    def set_table(self, table_name: str) -> None:
        """设置表名，自动添加配置中的前缀"""
        self._current_table = table_name
        self._full_table = f"{self.config.table_prefix}{table_name}" if self.config.table_prefix else table_name

    def _check_table_set(self) -> bool:
        """检查是否已设置表名"""
        if not self._full_table:
            print("错误：请先调用set_table方法设置表名")
            return False
        return True

    def connect(self) -> bool:
        """连接数据库"""
        self.disconnect()  # 先断开现有连接
        try:
            self.connection = mysql.connector.connect(**self.config.to_dict())
            if self.connection.is_connected():
                self.cursor = self.connection.cursor(dictionary=True)
                print(f"成功连接到数据库：{self.config.database}（节点：{self.config.section}）")
                return True
        except Error as e:
            print(f"数据库连接失败：{e}")
        return False

    def disconnect(self) -> None:
        """断开数据库连接"""
        if self.cursor:
            self.cursor.close()
            self.cursor = None
        if self.connection and self.connection.is_connected():
            self.connection.close()
            self.connection = None

    def switch_database(self, section: str) -> bool:
        """切换数据库节点并重新连接"""
        try:
            self.config.switch_section(section)
            return self.connect()
        except Exception as e:
            print(f"切换数据库失败：{e}")
            return False

    def execute_query(self, query: str, params: Tuple[Any, ...] = ()) -> Optional[List[Dict[str, Any]]]:
        """执行查询语句（内部通用方法）"""
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return None

        try:
            self.cursor.execute(query, params)
            return self.cursor.fetchall()
        except Error as e:
            print(f"查询执行失败：{e}")
            return None

    def execute_update(self, query: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
        """执行更新语句（INSERT/UPDATE/DELETE，内部通用方法）"""
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return None

        try:
            self.cursor.execute(query, params)
            self.connection.commit()
            return self.cursor.rowcount
        except Error as e:
            print(f"更新执行失败：{e}")
            self.connection.rollback()
            return None

    def insert_one(self, data: Dict[str, Any]) -> Optional[int]:
        """插入单条数据（自动使用带前缀的表名）"""
        if not self._check_table_set():
            return None
        
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO {self._full_table} ({columns}) VALUES ({placeholders})"
        return self.execute_update(query, tuple(data.values()))

    def update(self, data: Dict[str, Any], condition: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
        """更新数据（自动使用带前缀的表名）"""
        if not self._check_table_set():
            return None
        
        set_clause = ', '.join([f"{k} = %s" for k in data.keys()])
        query = f"UPDATE {self._full_table} SET {set_clause} WHERE {condition}"
        all_params = tuple(data.values()) + params
        return self.execute_update(query, all_params)

    def delete(self, condition: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
        """删除数据（自动使用带前缀的表名）"""
        if not self._check_table_set():
            return None
        
        query = f"DELETE FROM {self._full_table} WHERE {condition}"
        return self.execute_update(query, params)

    def select(self, columns: str = '*', condition: str = '', 
               params: Tuple[Any, ...] = (), order_by: str = '', 
               limit: int = 0) -> Optional[List[Dict[str, Any]]]:
        """查询数据（自动使用带前缀的表名，支持limit）"""
        if not self._check_table_set():
            return None
        
        query = f"SELECT {columns} FROM {self._full_table}"
        if condition:
            query += f" WHERE {condition}"
        if order_by:
            query += f" ORDER BY {order_by}"
        if limit > 0:
            query += f" LIMIT {limit}"
        return self.execute_query(query, params)

    def __del__(self):
        """对象销毁时自动断开连接"""
        self.disconnect()